/**
 * All Rights Reserved , Copyright (C) 2020 , 青岛鼎信通讯股份有限公司
 * <p>
 * PmCostIncomeService
 * 项目财务成本与确认收入
 * <p>
 * 修改纪录
 * 2020-05-19 版本：1.0 shenyouyi 创建。
 *
 * @version 版本：1.0
 * @author 作者：shenyouyi
 * 创建日期：2020-05-19
 */
package com.topscomm.pm.service.impl;

import com.topscomm.basic.exception.BusinessException;
import com.topscomm.basic.exception.ServiceException;
import com.topscomm.pm.pojo.PmCostIncomeEntity;
import com.topscomm.pm.pojo.PmProjectEntity;
import com.topscomm.pm.service.IPmCostIncomeService;
import com.topscomm.pm.service.IPmProjectService;
import com.topscomm.pm.service.impl.auto.PmCostIncomeServiceAuto;
import com.topscomm.pub.util.ConvertUtil;
import com.topscomm.pub.util.DateUtil;
import com.topscomm.pub.util.StringBufferProxy;
import com.topscomm.pub.util.StringUtil;
import com.topscomm.tap.common.SessionUserBean;
import com.topscomm.tap.threadlocal.ThreadLocalContext;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service(value = "pmCostIncomeService")
@Scope(value = "singleton")
public class PmCostIncomeService extends PmCostIncomeServiceAuto implements IPmCostIncomeService {

    @Autowired
    private IPmProjectService projectService;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void confirm(Map<String, Object> paraMap) {
        // 1.将所选的财政收入改为确认状态
        String ids = (String) paraMap.get("ids");
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle("update " + this.getDao().getTableName() + " set checked = 1 ");
        sql.appendSingle("where id in ({0})", ids);
        this.updateExecute(sql.toString());
        // 2.根据ids查询出关联的项目编码
        sql.clear();
        sql.appendSingle("select distinct projectid from " + this.getDao().getTableName());
        sql.appendSingle(" where id in ({0})", ids);
        List<Map<String, Object>> projectidMapList = this.selectExecute(sql.toString());
        String projectids = "";
        for (Map<String, Object> projectidMap : projectidMapList) {
            String projectid = projectidMap.get("projectid").toString();
            projectids = projectids + projectid + ",";
        }
        if (projectids.length() > 0) {
            projectids = projectids.substring(0, projectids.length() - 1);
        }
        // 3.根据项目ID汇总成本和收入
        sql.clear();
        sql.appendSingle("select projectid,sum(cost) as totalcost,sum(confirmincome) as totalincome ");
        sql.appendSingle("from " + this.getDao().getTableName());
        sql.appendSingle(" where checked=1 and projectid in ({0}) ", projectids);
        sql.appendSingle("group by projectid");
        List<Map<String, Object>> costIncomeList = this.selectExecute(sql.toString());
        // 4.更新项目的成本和收入
        Map<String, Object> updateFields = new HashMap<>();
        if (costIncomeList.size() > 0 && costIncomeList.get(0) != null && costIncomeList.get(0).get("projectid") != null) {
            for (Map<String, Object> costIncome : costIncomeList) {
                String projectid = costIncome.get("projectid").toString();
                BigDecimal totalcost = (BigDecimal) costIncome.get("totalcost");
                BigDecimal totalincome = (BigDecimal) costIncome.get("totalincome");
                updateFields.clear();
                updateFields.put("costmoney", totalcost);
                updateFields.put("confirmrevenuemoney", totalincome);
                sql.clear();
                sql.appendSingle("id = {0}", projectid);
                projectService.updateByWhere(updateFields, sql.toString());
            }
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void cancelConfirm(Map<String, Object> paraMap) {
        // 1.将当前记录设置为未确认
        String id = paraMap.get("id").toString();
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle("update " + this.getDao().getTableName() + " set checked = 0 ");
        sql.appendSingle("where id = {0}", id);
        this.updateExecute(sql.toString());
        // 2.查询当前关联项目的成本和收入
        PmCostIncomeEntity pmCostIncomeEntity = this.queryById(id);
        sql.clear();
        sql.appendSingle("select projectid,sum(cost) as totalcost,sum(confirmincome) as totalincome ");
        sql.appendSingle("from " + this.getDao().getTableName());
        sql.appendSingle(" where checked=1 and projectid = {0} ", pmCostIncomeEntity.getProjectid());
        sql.appendSingle("group by projectid");
        List<Map<String, Object>> costIncomeList = this.selectExecute(sql.toString());
        // 3.更新项目的成本和收入
        Long projectid = pmCostIncomeEntity.getProjectid();
        BigDecimal totalcost = BigDecimal.ZERO;
        BigDecimal totalincome = BigDecimal.ZERO;
        if (costIncomeList.size() > 0 && costIncomeList.get(0) != null && costIncomeList.get(0).get("projectid") != null) {
            totalcost = (BigDecimal) costIncomeList.get(0).get("totalcost");
            totalincome = (BigDecimal) costIncomeList.get(0).get("totalincome");
        }
        Map<String, Object> updateFields = new HashMap<>();
        updateFields.put("costmoney", totalcost);
        updateFields.put("confirmrevenuemoney", totalincome);
        sql.clear();
        sql.appendSingle("id = {0}", projectid);
        projectService.updateByWhere(updateFields, sql.toString());
    }

    @Override
    public void exportExcelXlsx(HttpServletResponse response, Map<String, String> paraMap) throws Exception {
        String sidx = ConvertUtil.convertToString(paraMap.get("sidx"));
        String sord = ConvertUtil.convertToString(paraMap.get("sord"));
        StringBufferProxy sql = new StringBufferProxy();
        sql.appendSingle((String) paraMap.get("sqlwhere"), new Object[0]);
        if (!StringUtil.isEmpty(sidx) && !StringUtil.isEmpty(sord)) {
            sql.appendSingle(" order by {0} {1} ", new Object[]{sidx, sord});
        }
        List<Map<String, Object>> listDataMap = this.queryMapByWhere(sql.toString());
        //文件路径
        String templatePath = "template/财务确认与收入模板.xlsx";
        ClassPathResource cpr = new ClassPathResource(templatePath);
        BufferedInputStream inputStream = new BufferedInputStream(cpr.getInputStream());
        //填充数据到模板
        XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
        Sheet sheet_target = xwb.getSheetAt(0);
        Row row;
        // 表行 从第三行开始
        int row_no = 2;
        String relation = "projectcode:1,financialmonth:2,cost:3,costexplain:4,confirmincome:5,incomeexplain:6";
        Map<String, String> keyMap = new HashMap<String, String>();
        for (String temp : relation.split(",")) {
            keyMap.put(temp.substring(0, temp.indexOf(":")), temp.substring(temp.indexOf(":") + 1));
        }
        //填充数据
        for (Map map : listDataMap) {
            row = sheet_target.getRow(row_no);
            if (row == null) {
                row = sheet_target.createRow(row_no);
                Row lastRow = sheet_target.getRow(row_no - 1);
                for (int i = 0; i < keyMap.size(); i++) {
                    Cell lastRowCell = lastRow.getCell(i);
                    row.createCell(i, lastRowCell.getCellType()).setCellStyle(lastRowCell.getCellStyle());
                }
            }
            for (String key : keyMap.keySet()) {
                if (key.equals("financialmonth")) {// 日期单独处理
                    row.getCell(ConvertUtil.convertToInt(keyMap.get(key)) - 1).setCellValue(DateUtil.dateToString((Date) map.get(key), "yyyy/MM/dd"));
                } else {
                    row.getCell(ConvertUtil.convertToInt(keyMap.get(key)) - 1).setCellValue(ConvertUtil.convertToString(map.get(key)));
                }
            }
            row_no++;
        }
        //不够两行的补充2行
        if (listDataMap.size() < 2) {
            row = sheet_target.getRow(row_no);
            for (String key : keyMap.keySet()) {
                row.getCell(ConvertUtil.convertToInt(keyMap.get(key)) - 1).setCellValue("");
            }
            row_no++;
            row = sheet_target.getRow(row_no);
            for (String key : keyMap.keySet()) {
                row.getCell(ConvertUtil.convertToInt(keyMap.get(key)) - 1).setCellValue("");
            }
            row_no++;
        }
        OutputStream out = response.getOutputStream();
        xwb.write(out);
        String fileName = ConvertUtil.convertToString(paraMap.get("fileName"));
        fileName = fileName.length() == 0 ? "导出数据.xlsx" : fileName + ".xlsx";
        response.setHeader("filename", fileName);
        if (inputStream != null) {
            inputStream.close();
        }
        if (out != null) {
            out.flush();
        }
    }

    @SneakyThrows
    @Override
    @Transactional(rollbackFor = {Exception.class})
    public int executeImportExcel(Map<String, Object> paraMap, File attachFile) {
        // 先检验第一行是否正确
        XSSFWorkbook wb = null;
        String rightTitle = "模板V1";
        try {
            InputStream input = new FileInputStream(attachFile);
            wb = new XSSFWorkbook(input);
            Sheet sheet = wb.getSheetAt(0);
            Row firstRow = sheet.getRow(0);
            String title = firstRow.getCell(0).getStringCellValue();
            if (!rightTitle.equals(title)) {
                throw new BusinessException("请使用模板文件填写内容");
            }
        } catch (Exception e) {
            throw new BusinessException(e);
        } finally {
            if (wb != null) {
                wb.close();
            }
        }
        try {
            return super.executeImportExcel(paraMap, attachFile);
        } catch (Exception e) {
            throw new BusinessException(e);
        }
    }

    @Override
    public void expandProcessExcelData(List<Map<String, String>> list) {
        super.expandProcessExcelData(list);
        String message = this.checkData(list);
        if (!StringUtil.isEmpty(message)) {
            throw new ServiceException(message);
        }
        message = this.checkProjectAndDate(list);
        if (!StringUtil.isEmpty(message)) {
            throw new ServiceException(message);
        }
        StringBufferProxy sql = new StringBufferProxy();
        list.stream().forEach(row -> {
            // 1.设置项目ID和项目名称
            String projectcode = row.get("projectcode");
            sql.clear();

            sql.appendSingle("code = '{0}'", projectcode);
            List<PmProjectEntity> pmProjectEntityList = projectService.queryByWhere(sql.toString());
            if (pmProjectEntityList.size() > 0) {
                PmProjectEntity pmProjectEntity = pmProjectEntityList.get(0);
                row.put("projectid", String.valueOf(pmProjectEntity.getId()));
                row.put("projectname", pmProjectEntity.getName());
            } else {
                row.put("projectcode", "");
            }
            // 2.日期截取到月
            String financialmonth = row.get("financialmonth");
            financialmonth = financialmonth.substring(0, 8) + "01 00:00:00";
            row.put("financialmonth", financialmonth);
            // 3.设置经办组织、经办部门和经办人
            SessionUserBean sessionUserBean = ThreadLocalContext.sessionUserBeanThreadLocal.get();
            row.put("orgname", sessionUserBean.getOrg().getName());
            row.put("orgcode", sessionUserBean.getOrg().getCode());
            row.put("deptname", sessionUserBean.getDept().getFullname());
            row.put("deptcode", sessionUserBean.getDept().getCode());
            row.put("username", sessionUserBean.getUser().getName());
            row.put("usercode", sessionUserBean.getUser().getCode());
        });
    }

    private String checkData(List<Map<String, String>> list) {
        StringBufferProxy result = new StringBufferProxy();
        int index = 3;
        String datePattern = "\\d{4}\\/(0?[1-9]|1[012])\\/(0?[1-9]|[12][0-9]|3[01]) ([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])";
        for (Map<String, String> row : list) {
            // 1.检验项目编码
            String projectcode = row.get("projectcode");
            boolean projectcodeEmpty = false;
            if (StringUtil.isEmpty(projectcode)) {
                projectcodeEmpty = true;
            }
            // 2.校验日期
            String financialmonth = row.get("financialmonth");
            boolean financialmonthError = false;
            if (StringUtil.isEmpty(financialmonth)) {
                financialmonthError = true;
            } else if (!financialmonth.matches(datePattern)) {
                financialmonthError = true;
            }
            // 3.校验月成本
            String cost = row.get("cost");
            boolean costError = false;
            if (StringUtil.isEmpty(cost)) {
                costError = true;
            }
            try {
                new BigDecimal(cost);
            } catch (Exception e) {
                costError = true;
            }
            // 4.检验确认收入
            String confirmincome = row.get("confirmincome");
            boolean confirmincomeError = false;
            if (StringUtil.isEmpty(confirmincome)) {
                confirmincomeError = true;
            }
            try {
                new BigDecimal(confirmincome);
            } catch (Exception e) {
                confirmincomeError = true;
            }
            if (projectcodeEmpty || financialmonthError || costError || confirmincomeError) {
                result.appendSingle("第{0}行", index);
                if (projectcodeEmpty) {
                    result.appendSingle("项目编码为空，");
                }
                if (financialmonthError) {
                    result.appendSingle("月份数据为空或无效，");
                }
                if (costError) {
                    result.appendSingle("月成本为空或者无效，");
                }
                if (confirmincomeError) {
                    result.appendSingle("确认收入为空或者无效，");
                }
                result = result.substring(0, result.length() - 1);
                result.appendSingle("。");
            }
            index++;
        }
        return result.toString();
    }

    /**
     * @return void
     * @author: shenyouyi
     * @date: 2020/7/30
     * @param: list
     * @description: 验证项目和日期的有效性
     * @modify:
     */
    private String checkProjectAndDate(List<Map<String, String>> list) {
        // 1.检验excel的数据是否重复
        Map<String, Long> groupMap = list.stream().map(row -> {
            Map<String, Object> map = new HashMap<>();
            // 1.设置项目ID和项目名称
            String projectcode = row.get("projectcode");
            map.put("projectcode", projectcode);
            // 2.日期截取到月
            String financialmonth = row.get("financialmonth");
            financialmonth = financialmonth.substring(0, 8) + "01 00:00:00";
            map.put("financialmonth", financialmonth);
            return map;
        }).collect(Collectors.groupingBy(map -> {
            return map.get("projectcode").toString() + ":" + map.get("financialmonth");
        }, Collectors.counting()));
        StringBufferProxy result = new StringBufferProxy();
        for (String projectAndMonth : groupMap.keySet()) {
            if (groupMap.get(projectAndMonth) > 1) {
                result.appendSingle(projectAndMonth).append(",");
            }
        }
        // 2.校验数据库中的数据是否重复
        StringBufferProxy sql = new StringBufferProxy();
        for (Map<String, String> row : list) {
            // 1.设置项目ID和项目名称
            String projectcode = row.get("projectcode");
            // 2.日期截取到月
            String financialmonth = row.get("financialmonth");
            financialmonth = financialmonth.substring(0, 8) + "01 00:00:00";
            sql.clear();
            sql.appendSingle("projectcode = '{0}' and financialmonth = to_date('{1}','yyyy/mm/dd hh24:mi:ss')", projectcode, financialmonth);
            int count = this.getCount(sql.toString());
            if (count > 0) {
                result.appendSingle(projectcode + ":" + financialmonth + ",");
            }
        }
        if (result.length() > 0) {
            result = result.substring(0, result.length() - 1);
            result.appendSingle(" 数据重复");
        }
        return result.toString();
    }


}
